-- **************************************************************************
--    Project Name:   干线车辆装载率错扫明细
--    Job Name:       jms_dm__dm_trunk_error_scan_detail
--    Description :   计算干线线路每个站点错扫的下一站的明细数据
--    Author :        禹铄
--    date：          2022/04/11
-- **************************************************************************
--    Relation :
--    jms_dm.dm_trunk_error_scan_detail << [
--        jms_dwd.dwd_tmsnew_shipment_dt,
--        jms_dwd.dwd_tmsnew_shipment_stop_dt,
--        jms_dwd.dwd_tab_barscan_loading_base_dt
--    ]
-- **************************************************************************

set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; --  每天生成 60 个分区
with tmp as (select a.waybill_no,
                    a.shipment_no,
                    a.scan_site_code,
                    a.scan_site,
                    a.next_station_code,
                    a.next_station,
                    a.scan_type,
                    a.scan_user,
                    a.scan_time,
                    b.planned_departure_time
             from jms_dwd.dwd_tab_barscan_loading_base_dt a
                      join jms_dwd.dwd_tmsnew_shipment_dt b
                           on a.shipment_no = b.shipment_no
             where a.dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
               and a.dt < date_add('{{ execution_date | cst_ds }}',1)
               and a.waybill_no is not null
               and substr(a.waybill_no, 1, 2) = 'JT'
               and b.dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
               and b.dt < date_add('{{ execution_date | cst_ds }}',1)
               and b.shipment_state <> 5
),
     ary as (
         select shipment_no, split(concat_ws('-', collect_list(network_code)), '-') as nextstation_ary
         from (
                  select shipment_no, network_code, to_date(create_time) as dt
                  from (select shipment_no, network_code, create_time
                        from jms_dwd.dwd_tmsnew_shipment_stop_dt
                        where dt = '{{ execution_date | cst_ds }}'
                          and table_type = 1) x --取出干线的数据
                  where to_date(create_time) >= date_sub(date_add('{{ execution_date | cst_ds }}',1), 30)
                    and to_date(create_time) <= date_add('{{ execution_date | cst_ds }}',1)
              ) y
         where dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -7)
           and dt < date_add('{{ execution_date | cst_ds }}',1)
         group by shipment_no)
insert overwrite table jms_dm.dm_trunk_error_scan_detail partition (dt)
select waybill_no,
       shipment_no,
       scan_site_code,
       scan_site,
       next_station_code,
       next_station,
       scan_type,
       scan_user,
       scan_time,
       nextstation_ary,
       date_format(planned_departure_time, 'yyyy-MM-dd') as dt
from (
         select y.waybill_no,
                shipment_no,
                scan_site_code,
                scan_site,
                next_station_code,
                next_station,
                scan_type,
                scan_user,
                scan_time,
                planned_departure_time,
                nextstation_ary,
                row_number()
                        over (partition by waybill_no , shipment_no,scan_site,scan_site_code order by scan_time desc) rk --多条错扫数据，取装车最晚的数据
         from (
                  select x.waybill_no,
                         shipment_no,
                         scan_site_code,
                         scan_site,
                         next_station_code,
                         next_station,
                         scan_type,
                         scan_user,
                         scan_time,
                         nextstation_ary,
                         planned_departure_time,
                         sum(flag)
                             over (partition by waybill_no,shipment_no,scan_site_code,scan_site) sm_flag -- 如果扫描的都匹配不到 则为0，如果有一个匹配到则>0
                  from (
                           select tmp.waybill_no,
                                  tmp.shipment_no,
                                  tmp.scan_site_code,
                                  tmp.scan_site,
                                  tmp.next_station_code,
                                  tmp.next_station,
                                  tmp.scan_type,
                                  tmp.scan_user,
                                  tmp.scan_time,
                                  tmp.planned_departure_time,
                                  ary.nextstation_ary,
                                  if(array_contains(ary.nextstation_ary, tmp.next_station_code), 1,
                                     0) flag -- 匹配到取1 匹配不到取0
                           from tmp
                                    join ary on tmp.shipment_no = ary.shipment_no) x) y
         where y.sm_flag = 0) k
where k.rk = 1
    distribute by 1;